﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class AddressDao
    {
        public bool Delete(SqlConnection conn, int addressId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tDELETE FROM [dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[AddressId] = @AddressId ");
            builder.Append("\t\tAND [UserId] IS NULL ");
            builder.Append("ELSE ");
            builder.Append("\tDELETE FROM [dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[AddressId] = @AddressId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@AddressId", SqlDbType.Int);
            commandParameters[0].Value = addressId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, AddressData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SET @Initial = dbo.ChinesePY(@Name) ");
            builder.Append("INSERT INTO [dbo].[Address] ");
            builder.Append("( ");
            builder.Append("\t[Initial], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[GroupId], ");
            builder.Append("\t[Name], ");
            builder.Append("\t[Sex], ");
            builder.Append("\t[NickName], ");
            builder.Append("\t[Birthday], ");
            builder.Append("\t[Ministration], ");
            builder.Append("\t[Mate], ");
            builder.Append("\t[Child], ");
            builder.Append("\t[DeptName], ");
            builder.Append("\t[DeptAddr], ");
            builder.Append("\t[DeptPost], ");
            builder.Append("\t[DeptTel], ");
            builder.Append("\t[DeptFax], ");
            builder.Append("\t[HomeAddr], ");
            builder.Append("\t[HomePost], ");
            builder.Append("\t[HomeTel], ");
            builder.Append("\t[Mobile], ");
            builder.Append("\t[Bp], ");
            builder.Append("\t[Email], ");
            builder.Append("\t[Oicq], ");
            builder.Append("\t[Icq], ");
            builder.Append("\t[Msn], ");
            builder.Append("\t[Note] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@Initial, ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@GroupId, ");
            builder.Append("\t@Name, ");
            builder.Append("\t@Sex, ");
            builder.Append("\t@NickName, ");
            builder.Append("\t@Birthday, ");
            builder.Append("\t@Ministration, ");
            builder.Append("\t@Mate, ");
            builder.Append("\t@Child, ");
            builder.Append("\t@DeptName, ");
            builder.Append("\t@DeptAddr, ");
            builder.Append("\t@DeptPost, ");
            builder.Append("\t@DeptTel, ");
            builder.Append("\t@DeptFax, ");
            builder.Append("\t@HomeAddr, ");
            builder.Append("\t@HomePost, ");
            builder.Append("\t@HomeTel, ");
            builder.Append("\t@Mobile, ");
            builder.Append("\t@Bp, ");
            builder.Append("\t@Email, ");
            builder.Append("\t@Oicq, ");
            builder.Append("\t@Icq, ");
            builder.Append("\t@Msn, ");
            builder.Append("\t@Note ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[0x19];
            commandParameters[0] = new SqlParameter("@Initial", SqlDbType.NChar, 1);
            if (data.InitialIsNull)
            {
                commandParameters[0].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0].Value = data.Initial;
            }
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.UserId;
            }
            commandParameters[2] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[2].Value = data.GroupId;
            commandParameters[3] = new SqlParameter("@Name", SqlDbType.NVarChar, 120);
            commandParameters[3].Value = data.Name;
            commandParameters[4] = new SqlParameter("@Sex", SqlDbType.Bit);
            if (data.SexIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Sex;
            }
            commandParameters[5] = new SqlParameter("@NickName", SqlDbType.NVarChar, 50);
            if (data.NickNameIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.NickName;
            }
            commandParameters[6] = new SqlParameter("@Birthday", SqlDbType.NVarChar, 20);
            if (data.BirthdayIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Birthday;
            }
            commandParameters[7] = new SqlParameter("@Ministration", SqlDbType.NVarChar, 50);
            if (data.MinistrationIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Ministration;
            }
            commandParameters[8] = new SqlParameter("@Mate", SqlDbType.NVarChar, 50);
            if (data.MateIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Mate;
            }
            commandParameters[9] = new SqlParameter("@Child", SqlDbType.NVarChar, 50);
            if (data.ChildIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Child;
            }
            commandParameters[10] = new SqlParameter("@DeptName", SqlDbType.NVarChar, 50);
            if (data.DeptNameIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.DeptName;
            }
            commandParameters[11] = new SqlParameter("@DeptAddr", SqlDbType.NVarChar, 200);
            if (data.DeptAddrIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.DeptAddr;
            }
            commandParameters[12] = new SqlParameter("@DeptPost", SqlDbType.NVarChar, 50);
            if (data.DeptPostIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.DeptPost;
            }
            commandParameters[13] = new SqlParameter("@DeptTel", SqlDbType.NVarChar, 50);
            if (data.DeptTelIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.DeptTel;
            }
            commandParameters[14] = new SqlParameter("@DeptFax", SqlDbType.NVarChar, 50);
            if (data.DeptFaxIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.DeptFax;
            }
            commandParameters[15] = new SqlParameter("@HomeAddr", SqlDbType.NVarChar, 200);
            if (data.HomeAddrIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.HomeAddr;
            }
            commandParameters[0x10] = new SqlParameter("@HomePost", SqlDbType.NVarChar, 50);
            if (data.HomePostIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.HomePost;
            }
            commandParameters[0x11] = new SqlParameter("@HomeTel", SqlDbType.NVarChar, 50);
            if (data.HomeTelIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.HomeTel;
            }
            commandParameters[0x12] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            if (data.MobileIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.Mobile;
            }
            commandParameters[0x13] = new SqlParameter("@Bp", SqlDbType.NVarChar, 50);
            if (data.BpIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.Bp;
            }
            commandParameters[20] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            if (data.EmailIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.Email;
            }
            commandParameters[0x15] = new SqlParameter("@Oicq", SqlDbType.NVarChar, 50);
            if (data.OicqIsNull)
            {
                commandParameters[0x15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x15].Value = data.Oicq;
            }
            commandParameters[0x16] = new SqlParameter("@Icq", SqlDbType.NVarChar, 50);
            if (data.IcqIsNull)
            {
                commandParameters[0x16].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x16].Value = data.Icq;
            }
            commandParameters[0x17] = new SqlParameter("@Msn", SqlDbType.NVarChar, 50);
            if (data.MsnIsNull)
            {
                commandParameters[0x17].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x17].Value = data.Msn;
            }
            commandParameters[0x18] = new SqlParameter("@Note", SqlDbType.NText);
            if (data.NoteIsNull)
            {
                commandParameters[0x18].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x18].Value = data.Note;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByAddressId(SqlConnection conn, int addressId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[AddressId], ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[Name], ");
            builder.Append("\t\t[Sex], ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t[Birthday], ");
            builder.Append("\t\t[Ministration], ");
            builder.Append("\t\t[Mate], ");
            builder.Append("\t\t[Child], ");
            builder.Append("\t\t[DeptName], ");
            builder.Append("\t\t[DeptAddr], ");
            builder.Append("\t\t[DeptPost], ");
            builder.Append("\t\t[DeptTel], ");
            builder.Append("\t\t[DeptFax], ");
            builder.Append("\t\t[HomeAddr], ");
            builder.Append("\t\t[HomePost], ");
            builder.Append("\t\t[HomeTel], ");
            builder.Append("\t\t[Mobile], ");
            builder.Append("\t\t[Bp], ");
            builder.Append("\t\t[Email], ");
            builder.Append("\t\t[Oicq], ");
            builder.Append("\t\t[Icq], ");
            builder.Append("\t\t[Msn], ");
            builder.Append("\t\t[Note] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[AddressId] = @AddressId ");
            builder.Append("\t\tAND [UserId] IS NULL ");
            builder.Append("ELSE ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t[AddressId], ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[Name], ");
            builder.Append("\t\t[Sex], ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t[Birthday], ");
            builder.Append("\t\t[Ministration], ");
            builder.Append("\t\t[Mate], ");
            builder.Append("\t\t[Child], ");
            builder.Append("\t\t[DeptName], ");
            builder.Append("\t\t[DeptAddr], ");
            builder.Append("\t\t[DeptPost], ");
            builder.Append("\t\t[DeptTel], ");
            builder.Append("\t\t[DeptFax], ");
            builder.Append("\t\t[HomeAddr], ");
            builder.Append("\t\t[HomePost], ");
            builder.Append("\t\t[HomeTel], ");
            builder.Append("\t\t[Mobile], ");
            builder.Append("\t\t[Bp], ");
            builder.Append("\t\t[Email], ");
            builder.Append("\t\t[Oicq], ");
            builder.Append("\t\t[Icq], ");
            builder.Append("\t\t[Msn], ");
            builder.Append("\t\t[Note] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[AddressId] = @AddressId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@AddressId", SqlDbType.Int);
            commandParameters[0].Value = addressId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Address";
            return set.Tables[0];
        }

        public DataTable SelectByGroupId(SqlConnection conn, int groupId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[AddressId], ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[Name], ");
            builder.Append("\t\t[Sex], ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t[Birthday], ");
            builder.Append("\t\t[Ministration], ");
            builder.Append("\t\t[Mate], ");
            builder.Append("\t\t[Child], ");
            builder.Append("\t\t[DeptName], ");
            builder.Append("\t\t[DeptAddr], ");
            builder.Append("\t\t[DeptPost], ");
            builder.Append("\t\t[DeptTel], ");
            builder.Append("\t\t[DeptFax], ");
            builder.Append("\t\t[HomeAddr], ");
            builder.Append("\t\t[HomePost], ");
            builder.Append("\t\t[HomeTel], ");
            builder.Append("\t\t[Mobile], ");
            builder.Append("\t\t[Bp], ");
            builder.Append("\t\t[Email], ");
            builder.Append("\t\t[Oicq], ");
            builder.Append("\t\t[Icq], ");
            builder.Append("\t\t[Msn], ");
            builder.Append("\t\t[Note] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[GroupId] = @GroupId ");
            builder.Append("\t\tAND [UserId] IS NULL ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[AddressId] ASC ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[AddressId], ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[Name], ");
            builder.Append("\t\t[Sex], ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t[Birthday], ");
            builder.Append("\t\t[Ministration], ");
            builder.Append("\t\t[Mate], ");
            builder.Append("\t\t[Child], ");
            builder.Append("\t\t[DeptName], ");
            builder.Append("\t\t[DeptAddr], ");
            builder.Append("\t\t[DeptPost], ");
            builder.Append("\t\t[DeptTel], ");
            builder.Append("\t\t[DeptFax], ");
            builder.Append("\t\t[HomeAddr], ");
            builder.Append("\t\t[HomePost], ");
            builder.Append("\t\t[HomeTel], ");
            builder.Append("\t\t[Mobile], ");
            builder.Append("\t\t[Bp], ");
            builder.Append("\t\t[Email], ");
            builder.Append("\t\t[Oicq], ");
            builder.Append("\t\t[Icq], ");
            builder.Append("\t\t[Msn], ");
            builder.Append("\t\t[Note] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[GroupId] = @GroupId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[AddressId] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = groupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Address";
            return set.Tables[0];
        }

        public DataTable SelectByPY(SqlConnection conn, string initial, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[AddressId], ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[Name], ");
            builder.Append("\t\t[Sex], ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t[Birthday], ");
            builder.Append("\t\t[Ministration], ");
            builder.Append("\t\t[Mate], ");
            builder.Append("\t\t[Child], ");
            builder.Append("\t\t[DeptName], ");
            builder.Append("\t\t[DeptAddr], ");
            builder.Append("\t\t[DeptPost], ");
            builder.Append("\t\t[DeptTel], ");
            builder.Append("\t\t[DeptFax], ");
            builder.Append("\t\t[HomeAddr], ");
            builder.Append("\t\t[HomePost], ");
            builder.Append("\t\t[HomeTel], ");
            builder.Append("\t\t[Mobile], ");
            builder.Append("\t\t[Bp], ");
            builder.Append("\t\t[Email], ");
            builder.Append("\t\t[Oicq], ");
            builder.Append("\t\t[Icq], ");
            builder.Append("\t\t[Msn], ");
            builder.Append("\t\t[Note] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[Initial] = @Initial ");
            builder.Append("\t\tAND [UserId] IS NULL ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[AddressId], ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\t[UserId], ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[Name], ");
            builder.Append("\t\t[Sex], ");
            builder.Append("\t\t[NickName], ");
            builder.Append("\t\t[Birthday], ");
            builder.Append("\t\t[Ministration], ");
            builder.Append("\t\t[Mate], ");
            builder.Append("\t\t[Child], ");
            builder.Append("\t\t[DeptName], ");
            builder.Append("\t\t[DeptAddr], ");
            builder.Append("\t\t[DeptPost], ");
            builder.Append("\t\t[DeptTel], ");
            builder.Append("\t\t[DeptFax], ");
            builder.Append("\t\t[HomeAddr], ");
            builder.Append("\t\t[HomePost], ");
            builder.Append("\t\t[HomeTel], ");
            builder.Append("\t\t[Mobile], ");
            builder.Append("\t\t[Bp], ");
            builder.Append("\t\t[Email], ");
            builder.Append("\t\t[Oicq], ");
            builder.Append("\t\t[Icq], ");
            builder.Append("\t\t[Msn], ");
            builder.Append("\t\t[Note] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[Initial] = @Initial ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@Initial", SqlDbType.NChar, 1);
            commandParameters[0].Value = initial.Substring(0, 1);
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Address";
            return set.Tables[0];
        }

        public DataTable SelectPY(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE  @TempTable TABLE (ID int identity(1,1), Initial nchar(1), Nums int) ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tINSERT INTO @TempTable ");
            builder.Append("\tSELECT\t\t ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\tCOUNT([Initial]) AS Nums ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UserId] IS NULL ");
            builder.Append("\t\t AND [Initial] <> '' ");
            builder.Append("\tGROUP BY ");
            builder.Append("\t\t[Initial] ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[Initial] ASC ");
            builder.Append("ELSE ");
            builder.Append("\tINSERT INTO @TempTable ");
            builder.Append("\tSELECT\t\t ");
            builder.Append("\t\t[Initial], ");
            builder.Append("\t\tCOUNT([Initial]) AS Nums ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UserId]  = @UserId ");
            builder.Append("\t\t AND [Initial] <> '' ");
            builder.Append("\tGROUP BY ");
            builder.Append("\t\t[Initial] ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[Initial] ASC ");
            builder.Append("SELECT * FROM @TempTable ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            if (userId == string.Empty)
            {
                commandParameters[0].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Address";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, AddressData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SET @Initial = dbo.ChinesePY(@Name) ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tUPDATE [dbo].[Address] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[Initial] = @Initial, ");
            builder.Append("\t\t[UserId] = @UserId, ");
            builder.Append("\t\t[GroupId] = @GroupId, ");
            builder.Append("\t\t[Name] = @Name, ");
            builder.Append("\t\t[Sex] = @Sex, ");
            builder.Append("\t\t[NickName] = @NickName, ");
            builder.Append("\t\t[Birthday] = @Birthday, ");
            builder.Append("\t\t[Ministration] = @Ministration, ");
            builder.Append("\t\t[Mate] = @Mate, ");
            builder.Append("\t\t[Child] = @Child, ");
            builder.Append("\t\t[DeptName] = @DeptName, ");
            builder.Append("\t\t[DeptAddr] = @DeptAddr, ");
            builder.Append("\t\t[DeptPost] = @DeptPost, ");
            builder.Append("\t\t[DeptTel] = @DeptTel, ");
            builder.Append("\t\t[DeptFax] = @DeptFax, ");
            builder.Append("\t\t[HomeAddr] = @HomeAddr, ");
            builder.Append("\t\t[HomePost] = @HomePost, ");
            builder.Append("\t\t[HomeTel] = @HomeTel, ");
            builder.Append("\t\t[Mobile] = @Mobile, ");
            builder.Append("\t\t[Bp] = @Bp, ");
            builder.Append("\t\t[Email] = @Email, ");
            builder.Append("\t\t[Oicq] = @Oicq, ");
            builder.Append("\t\t[Icq] = @Icq, ");
            builder.Append("\t\t[Msn] = @Msn, ");
            builder.Append("\t\t[Note] = @Note ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[AddressId] = @AddressId ");
            builder.Append("                 \t AND [UserId] IS NULL ");
            builder.Append("ELSE ");
            builder.Append("\tUPDATE [dbo].[Address] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[Initial] = @Initial, ");
            builder.Append("\t\t[UserId] = @UserId, ");
            builder.Append("\t\t[GroupId] = @GroupId, ");
            builder.Append("\t\t[Name] = @Name, ");
            builder.Append("\t\t[Sex] = @Sex, ");
            builder.Append("\t\t[NickName] = @NickName, ");
            builder.Append("\t\t[Birthday] = @Birthday, ");
            builder.Append("\t\t[Ministration] = @Ministration, ");
            builder.Append("\t\t[Mate] = @Mate, ");
            builder.Append("\t\t[Child] = @Child, ");
            builder.Append("\t\t[DeptName] = @DeptName, ");
            builder.Append("\t\t[DeptAddr] = @DeptAddr, ");
            builder.Append("\t\t[DeptPost] = @DeptPost, ");
            builder.Append("\t\t[DeptTel] = @DeptTel, ");
            builder.Append("\t\t[DeptFax] = @DeptFax, ");
            builder.Append("\t\t[HomeAddr] = @HomeAddr, ");
            builder.Append("\t\t[HomePost] = @HomePost, ");
            builder.Append("\t\t[HomeTel] = @HomeTel, ");
            builder.Append("\t\t[Mobile] = @Mobile, ");
            builder.Append("\t\t[Bp] = @Bp, ");
            builder.Append("\t\t[Email] = @Email, ");
            builder.Append("\t\t[Oicq] = @Oicq, ");
            builder.Append("\t\t[Icq] = @Icq, ");
            builder.Append("\t\t[Msn] = @Msn, ");
            builder.Append("\t\t[Note] = @Note ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[AddressId] = @AddressId ");
            builder.Append("\t              AND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[0x1a];
            commandParameters[0] = new SqlParameter("@AddressId", SqlDbType.Int);
            commandParameters[0].Value = data.AddressId;
            commandParameters[1] = new SqlParameter("@Initial", SqlDbType.NChar, 1);
            if (data.InitialIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.Initial;
            }
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.UserId;
            }
            commandParameters[3] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[3].Value = data.GroupId;
            commandParameters[4] = new SqlParameter("@Name", SqlDbType.NVarChar, 120);
            commandParameters[4].Value = data.Name;
            commandParameters[5] = new SqlParameter("@Sex", SqlDbType.Bit);
            if (data.SexIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Sex;
            }
            commandParameters[6] = new SqlParameter("@NickName", SqlDbType.NVarChar, 50);
            if (data.NickNameIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.NickName;
            }
            commandParameters[7] = new SqlParameter("@Birthday", SqlDbType.NVarChar, 20);
            if (data.BirthdayIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Birthday;
            }
            commandParameters[8] = new SqlParameter("@Ministration", SqlDbType.NVarChar, 50);
            if (data.MinistrationIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Ministration;
            }
            commandParameters[9] = new SqlParameter("@Mate", SqlDbType.NVarChar, 50);
            if (data.MateIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Mate;
            }
            commandParameters[10] = new SqlParameter("@Child", SqlDbType.NVarChar, 50);
            if (data.ChildIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.Child;
            }
            commandParameters[11] = new SqlParameter("@DeptName", SqlDbType.NVarChar, 50);
            if (data.DeptNameIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.DeptName;
            }
            commandParameters[12] = new SqlParameter("@DeptAddr", SqlDbType.NVarChar, 200);
            if (data.DeptAddrIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.DeptAddr;
            }
            commandParameters[13] = new SqlParameter("@DeptPost", SqlDbType.NVarChar, 50);
            if (data.DeptPostIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.DeptPost;
            }
            commandParameters[14] = new SqlParameter("@DeptTel", SqlDbType.NVarChar, 50);
            if (data.DeptTelIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.DeptTel;
            }
            commandParameters[15] = new SqlParameter("@DeptFax", SqlDbType.NVarChar, 50);
            if (data.DeptFaxIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.DeptFax;
            }
            commandParameters[0x10] = new SqlParameter("@HomeAddr", SqlDbType.NVarChar, 200);
            if (data.HomeAddrIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.HomeAddr;
            }
            commandParameters[0x11] = new SqlParameter("@HomePost", SqlDbType.NVarChar, 50);
            if (data.HomePostIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.HomePost;
            }
            commandParameters[0x12] = new SqlParameter("@HomeTel", SqlDbType.NVarChar, 50);
            if (data.HomeTelIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.HomeTel;
            }
            commandParameters[0x13] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            if (data.MobileIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.Mobile;
            }
            commandParameters[20] = new SqlParameter("@Bp", SqlDbType.NVarChar, 50);
            if (data.BpIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.Bp;
            }
            commandParameters[0x15] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            if (data.EmailIsNull)
            {
                commandParameters[0x15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x15].Value = data.Email;
            }
            commandParameters[0x16] = new SqlParameter("@Oicq", SqlDbType.NVarChar, 50);
            if (data.OicqIsNull)
            {
                commandParameters[0x16].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x16].Value = data.Oicq;
            }
            commandParameters[0x17] = new SqlParameter("@Icq", SqlDbType.NVarChar, 50);
            if (data.IcqIsNull)
            {
                commandParameters[0x17].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x17].Value = data.Icq;
            }
            commandParameters[0x18] = new SqlParameter("@Msn", SqlDbType.NVarChar, 50);
            if (data.MsnIsNull)
            {
                commandParameters[0x18].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x18].Value = data.Msn;
            }
            commandParameters[0x19] = new SqlParameter("@Note", SqlDbType.NText);
            if (data.NoteIsNull)
            {
                commandParameters[0x19].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x19].Value = data.Note;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

